//
//  DbManagers.m
//  Flight
//
//  Created by haiming on 13-8-7.
//  Copyright (c) 2013年 haiming. All rights reserved.
//

#import "DbManager.h"

static DbManager* dbManager = nil;
@implementation DbManager
@synthesize dataBase;

//创建单例
+ (id)sharedManager
{
	@synchronized(self) {
		if (dbManager == nil)
			dbManager = [[self alloc] initWithDbName:DBNAME];
	}
	return dbManager;
}

+ (id)allocWithZone:(NSZone *)zone
{
	@synchronized(self) {
		if (dbManager == nil) {
			dbManager = [super allocWithZone:zone];
		}
	}
	return dbManager;
    
	return nil;
}

//创建数据库路径
- (NSString*) getFilePath:(NSString *)filename
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDirectory = [paths objectAtIndex:0];
    NSString *path = [documentDirectory stringByAppendingPathComponent:filename];
    CLog(@"%@",path);
    return path;
}

//重写init方法
- (id)initWithDbName:(NSString *)name
{
    if (self = [super init]) {
        dataBase = [FMDatabase databaseWithPath:[self getFilePath:name]];
        [self createTables];
        [self getFilePath:name];
    }
   
    return self;
}

//创建表
-(void)createTables
{
    if ([dataBase open]) {
        [dataBase executeUpdate:@"CREATE TABLE cuisines (id varchar,lx_name varchar)"];
        [dataBase executeUpdate:@"CREATE TABLE area (id varchar, xianqu varchar)"];
        [dataBase executeUpdate:@"CREATE TABLE circle (id varchar, area_id varchar, lx_name varchar)"];
        [dataBase executeUpdate:@"CREATE TABLE orders (title varchar, book_type varchar)"];
        [dataBase executeUpdate:@"CREATE TABLE distance (juli varchar, Display varchar)"];
        [dataBase executeUpdate:@"CREATE TABLE likes (id varchar, type varchar)"];
    }
}

- (void)clearMenuList
{
    if ([dataBase open]) {
        [dataBase executeUpdate:@"DELETE FROM cuisines"];
        [dataBase executeUpdate:@"DELETE FROM area"];
        [dataBase executeUpdate:@"DELETE FROM circle"];
        [dataBase executeUpdate:@"DELETE FROM orders"];
        [dataBase executeUpdate:@"DELETE FROM distance"];
    }
}

- (BOOL)hasList
{
    NSString *sql = @"SELECT * FROM area limit 1";
    [dataBase open];
 
    FMResultSet* rs = [dataBase executeQuery:sql];
    while ([rs next]) {
        return YES;
    }
    
    [dataBase close];
    
    return NO;
}

//插入菜系数据
-(void)insertCuisines:(NSArray*)array
{
    @synchronized(self) {
        if ([dataBase open])
        {
            [dataBase beginTransaction];
            for (int i = 0; i < [array count] ; i++)
            {
                NSDictionary *dic = [array objectAtIndex:i];
                NSString *lx_name = [dic objectForKey:@"lx_name"];
                NSString *cid = [dic objectForKey:@"id"];
                
                if (lx_name == nil || [@"" isEqualToString:lx_name]) {
                    continue;
                }
                
                [dataBase executeUpdate:@"INSERT INTO cuisines (id, lx_name) VALUES (?,?)", cid,lx_name];
            }
            [dataBase commit];
        }
    }
}

//插入城市数据
-(void)insertAreas:(NSArray*)array
{
    @synchronized(self) {
        [dataBase beginTransaction];
        for (int i = 0; i < [array count] ; i++)
        {
            NSDictionary *dic = [array objectAtIndex:i];
            NSString *xianqu = [dic objectForKey:@"xianqu"];
            NSString *cid = [dic objectForKey:@"id"];
            NSArray *circles = [dic objectForKey:@"dshangquan"];
            
            if ([self existsArea:dic] == 0) {
                [dataBase executeUpdate:@"INSERT INTO area (id, xianqu) VALUES (?,?)", cid, xianqu];
                [self insertCircles:circles withAreaId:cid];
            }
        }
        [dataBase commit];
    }
}

- (int)existsArea:(NSDictionary *)dic
{
    NSNumber *cid = [NSNumber numberWithInt:[[dic objectForKey:@"id"] intValue]];
    FMResultSet *rs = [dataBase executeQuery:@"SELECT COUNT(0) FROM area WHERE id = ?",cid];
    while ([rs next]) {
        return [rs intForColumnIndex:0];
    }
    
    return 0;
}

//插入商圈数据
-(void)insertCircles:(NSArray*)array withAreaId:(NSString *)areaId
{
    for (int i = 0; i < [array count] ; i++)
    {
        NSDictionary *dic = [array objectAtIndex:i];
        NSString *lx_name = [dic objectForKey:@"lx_name"];
        NSNumber *cid = [dic objectForKey:@"id"];
        
        if ([self existsCircle:dic] == 0) {
            [dataBase executeUpdate:@"INSERT INTO circle (id, area_id, lx_name) VALUES (?,?,?)", cid, areaId, lx_name];
        }
    }
}

- (int)existsCircle:(NSDictionary *)dic
{
    NSNumber *cid = [NSNumber numberWithInt:[[dic objectForKey:@"id"] intValue]];
    FMResultSet *rs = [dataBase executeQuery:@"SELECT COUNT(0) FROM circle WHERE id = ?",cid];
    while ([rs next]) {
        return [rs intForColumnIndex:0];
    }
    
    return 0;
}

//插入商圈数据
-(void)insertOrders:(NSArray *)array
{
    for (int i = 0; i < [array count] ; i++)
    {
        NSDictionary *dic = [array objectAtIndex:i];
        NSString *title = [dic objectForKey:@"title"];
        NSString *book_type = [dic objectForKey:@"book_type"];
        
        if ([self existsOrder:dic] == 0) {
            [dataBase executeUpdate:@"INSERT INTO orders (title, book_type) VALUES (?,?)", title, book_type];
        }
    }
}

- (int)existsOrder:(NSDictionary *)dic
{
    NSString *title = [dic objectForKey:@"title"];
    NSString *book_type = [dic objectForKey:@"book_type"];
    
    FMResultSet *rs = [dataBase executeQuery:@"SELECT COUNT(0) FROM orders WHERE title = ? and book_type = ?",title, book_type];
    while ([rs next]) {
        return [rs intForColumnIndex:0];
    }
    
    return 0;
}

//插入距离数据
- (void)insertDistances:(NSArray *)array
{
    for (int i = 0; i < [array count] ; i++)
    {
        NSDictionary *dic = [array objectAtIndex:i];
        NSString *juli = [dic objectForKey:@"juli"];
        NSString *display = [dic objectForKey:@"Display"];
        
        if ([self existsDistances:dic] == 0) {
            [dataBase executeUpdate:@"INSERT INTO distance (juli, Display) VALUES (?,?)", juli, display];
        }
    }
}

- (int)existsDistances:(NSDictionary *)dic
{
    NSString *juli = [dic objectForKey:@"juli"];
    NSString *display = [dic objectForKey:@"Display"];
    
    FMResultSet *rs = [dataBase executeQuery:@"SELECT COUNT(0) FROM distance WHERE juli = ? and Display = ?",juli, display];
    while ([rs next]) {
        return [rs intForColumnIndex:0];
    }
    
    return 0;
}

- (void)insertLike:(NSString *)sid type:(LikeType)type
{
    NSString *sql = @"INSERT INTO likes(id,type) values(?,?)";
    [dataBase executeUpdate:sql, sid, INT_TO_STR(type)];
}

- (BOOL)isLike:(NSString *)sid type:(LikeType)type
{
    NSString *sql = @"SELECT COUNT(0) FROM likes WHERE id=? AND type=?";
    
    [dataBase open];
    FMResultSet *rs = [dataBase executeQuery:sql, sid, INT_TO_STR(type)];
    while ([rs next]) {
        int count = [rs intForColumnIndex:0];
        CLog(@"count :%i",count);
        return count > 0;
    }
    
    return NO;
}

- (void)clearLikes
{
    NSDate *now = [NSDate date];
    NSString *nowStr = [now toStringWithFormat:@"yyyyMMdd"];
    if (![nowStr isEqualToString:[AppSetting clearLikeDate]]) {
        NSString *sql = @"delete from likes";
        [dataBase executeUpdate:sql];
        
        [AppSetting setClearLikeDate];
    }
}

//查询
-(NSMutableArray*)fetchCuisines
{
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
  
    NSString *sql = @"SELECT * FROM cuisines";
   [dataBase open];
    FMResultSet* rs = [dataBase executeQuery:sql];

    while ([rs next]) {
        NSString* _id = [rs stringForColumn:@"id"];
        NSString* lx_name = [rs stringForColumn:@"lx_name"];
        NSDictionary * dic = [NSDictionary
                              dictionaryWithObjectsAndKeys:_id,@"id", lx_name, @"lx_name", nil];
        [array addObject:dic];

    }
   [dataBase close];
    
    return array;
}

-(NSMutableArray*)fetchAreas
{
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
    
    NSString *sql = @"SELECT * FROM area";
    [dataBase open];
    FMResultSet* rs = [dataBase executeQuery:sql];
    
    while ([rs next]) {
        NSString* _id = [rs stringForColumn:@"id"];
        NSString* area = [rs stringForColumn:@"xianqu"];
        NSDictionary * dic = [NSDictionary
                              dictionaryWithObjectsAndKeys:_id,@"id", area, @"xianqu", nil];
        [array addObject:dic];
        
    }
    [dataBase close];
    
    return array;
}

-(NSMutableArray*)fetchCirclesWithAreaId:(NSString*)areaId
{
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
    NSDictionary *dic = [NSDictionary
                         dictionaryWithObjectsAndKeys:@"",@"id", @"全部", @"lx_name", nil];
    [array addObject:dic];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM circle where area_id=%@",areaId];
    if ([areaId isEqualToString:@""]) {
        sql = @"SELECT * FROM circle";
    }
    
    [dataBase open];
    FMResultSet* rs = [dataBase executeQuery:sql];
    
    while ([rs next]) {
        NSString* _id = [rs stringForColumn:@"id"];
        NSString* lx_name = [rs stringForColumn:@"lx_name"];
        
        if (!lx_name || [lx_name isEqualToString:@""]) {
            continue;
        }
        
        NSDictionary * dic = [NSDictionary
                              dictionaryWithObjectsAndKeys:_id,@"id", lx_name, @"lx_name", nil];
        [array addObject:dic];
    }
    [dataBase close];
    
    return array;
}


-(NSMutableArray*)fetchOrders
{
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
    
    NSString *sql = @"SELECT * FROM orders";
    
    [dataBase open];
    FMResultSet* rs = [dataBase executeQuery:sql];
    while ([rs next]) {
        NSString* title = [rs stringForColumn:@"title"];
        NSString* book_type = [rs stringForColumn:@"book_type"];
        NSDictionary * dic = [NSDictionary
                              dictionaryWithObjectsAndKeys:title,@"title", book_type, @"book_type", nil];
        [array addObject:dic];
    }
    [dataBase close];
    
    return array;
}

-(NSMutableArray*)fetchDistances
{
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
    
    NSString *sql = @"SELECT * FROM distance";
    
    [dataBase open];
    FMResultSet* rs = [dataBase executeQuery:sql];
    while ([rs next]) {
        NSString* juli = [rs stringForColumn:@"juli"];
        NSString* display = [rs stringForColumn:@"Display"];
        NSDictionary * dic = [NSDictionary
                              dictionaryWithObjectsAndKeys:juli,@"juli", display, @"display", nil];
        [array addObject:dic];
    }
    [dataBase close];
    
    return array;
}

-(NSArray*)searchAirData:(NSString*)str
{
    [dataBase open];
    NSMutableArray* data_count = [NSMutableArray arrayWithCapacity:10];
    FMResultSet* ar = [dataBase executeQuery:@"SELECT * FROM AirInfo"];
    while ([ar next]) {
        NSString* code = [ar stringForColumn:@"code"];
        [data_count addObject:code];
    }
    CLog(@"data_count:%i",[data_count count]);
    NSMutableArray* array = [NSMutableArray arrayWithCapacity:10];
    NSMutableString *string = [NSMutableString stringWithString:@"SELECT * FROM AirInfo WHERE "];
    [string appendFormat:@"cn  like '%@",str];
    [string appendString:@"%'"];
    [string appendFormat:@" or en  like '%@",str];
    [string appendString:@"%'"];
    [string appendFormat:@" or code  like '%@",str];
    [string appendString:@"%' limit 10"];
    
    
    FMResultSet* rs = [dataBase executeQuery:string];
    
    while ([rs next])
    {
        NSString* cn = [rs stringForColumn:@"cn"];
        NSString* en = [rs stringForColumn:@"en"];
        NSString* code = [rs stringForColumn:@"code"];
        NSDictionary * dic = [NSDictionary dictionaryWithObjectsAndKeys:cn,@"cn",en,@"en",code,@"code", nil];
        [array addObject:dic];
        
    }
    NSArray * arr = array;
    [dataBase close];
    return arr;
}



- (void)dealloc
{
    if ([dataBase open]) [dataBase close];
}

@end
